﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using BlueLotus7.ArchiveSystem.Common;
using BlueLotus7.Eden.Service.Properties;
using BlueLotus7.Common;

namespace BlueLotus7.Eden.Service.BusinessLayer
{
    public class BaseDataAdapter
    {
        #region Columns

        protected const string Column_Key = "Key";
        protected const string Column_Code = "Code";
        protected const string Column_Type = "Type";
        protected const string Column_Stamp = "Stamp";
        protected const string Column_Name = "Name";
        protected const string Column_Description = "Description";
        protected const string Column_DomainScopeKey = "DomainScopeKey";
        protected const string Column_DomainScopeUserKey = "DomainScopeUserKey";
        protected const string Column_PersonKey = "PersonKey";
        protected const string Column_ThirdPartyIdentity = "ThirdPartyIdentity";
        protected const string Column_LastUpdatedStamp = "LastUpdatedStamp";
        protected const string Column_Status = "Status";
        protected const string Column_Keyword = "Keyword";
        protected const string Column_MonitorStatus = "MonitorStatus";

        protected const string Column_CreatedStamp = "CreatedStamp";
        protected const string Column_LastSyncStamp = "LastSyncStamp";
        protected const string Column_LastExecutionStamp = "LastExecutionStamp";
        protected const string Column_ClientIdentity = "ClientIdentity";
        protected const string Column_SettingStamp = "SettingStamp";
        protected const string Column_ClientKey = "ClientKey";
        protected const string Column_TopCount = "TopCount";
        protected const string Column_AssignedToClientKey = "AssignedToClientKey";

        protected const string Column_StartStamp = "StartStamp";
        protected const string Column_EndStamp = "EndStamp";
        protected const string Column_ExecutionType = "ExecutionType";
        protected const string Column_DomainScopeCode = "DomainScopeCode";
        protected const string Column_IsNightOperationOnlye = "IsNightOperationOnly";
        protected const string Column_IsDisabled = "IsDisabled";
        protected const string Column_Options = "Options";
        protected const string Column_IsSuccess = "IsSuccess";
        protected const string Column_Exception = "Exception";
        protected const string Column_TaskKey = "TaskKey";

        protected const string Column_Url = "Url";
        protected const string Column_ActualUrl = "ActualUrl";
        protected const string Column_Title = "Title";
        protected const string Column_SourceUrl = "SourceUrl";
        protected const string Column_SourceName = "SourceName";
        protected const string Column_FileName = "FileName";
        protected const string Column_FileExtension = "FileExtension";
        protected const string Column_FileSize = "FileSize";
        protected const string Column_MD5 = "MD5";
        protected const string Column_Tags = "Tags";

        protected const string Column_StartGuid = "StartGuid";
        protected const string Column_EndGuid = "EndGuid";
        protected const string Column_OrderDESC = "OrderDESC";


        #endregion

        #region Prefix

        protected const string Prefix_Record = "Record";
        protected const string Prefix_Task = "Task";
        protected const string Prefix_Client = "Client";
        protected const string Prefix_DomainScope = "DomainScope";
        protected const string Prefix_DomainScopeUser = "DomainScopeUser";

        #endregion

        protected SqlConnection CreateSqlConnection()
        {
            return new SqlConnection(Settings.Default.SqlConnection);
        }


        #region Constructor

        public BaseDataAdapter()
        {

        }

        #endregion

        #region Third Party Url

        /// <summary>
        /// Adds or update <c>ThridPartyUrl</c> object.
        /// </summary>
        /// <param name="thirdPartyUrl"></param>
        public void AddOrUpdateThirdPartyUrl(ThridPartyUrl thirdPartyUrl)
        {
            const string spName = "sp_AddOrUpdateThirdPartyUrl";

            if (thirdPartyUrl != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Url), thirdPartyUrl.Url);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ActualUrl), thirdPartyUrl.ActualUrl);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Title), thirdPartyUrl.Title);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), thirdPartyUrl.Description);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
                parameters.Add(parameter);


                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());

                try
                {
                    databaseOperator.ExecuteNonQuery(spName, parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }
        }

        /// <summary>
        /// Adds or update <c>ThridPartyUrl</c> object.
        /// </summary>
        /// <param name="thirdPartyUrl"></param>
        public ThridPartyUrl GetThirdPartyUrl(string thirdPartyUrl)
        {
            const string spName = "sp_GetThirdPartyUrl";

            ThridPartyUrl result = null;
            if (!string.IsNullOrWhiteSpace(thirdPartyUrl))
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Url), thirdPartyUrl);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                SqlDataReader reader = null;

                try
                {
                    reader = databaseOperator.ExecuteReader(spName, parameters);
                    List<ThridPartyUrl> urls = ReadThridPartyUrl(reader);
                    if (urls != null && urls.Count > 0)
                    {
                        result = urls[0];
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    databaseOperator.Dispose();
                }
            }
            return result;
        }

        #endregion

        #region Files

        /// <summary>
        /// Adds or update <c>ArchivedBinaryArticle</c> object.
        /// </summary>
        /// <param name="thirdPartyUrl"></param>
        public void AddOrUpdateFile(ArchivedBinaryArticle binaryFileInfo)
        {
            const string spName = "sp_AddOrUpdateFile";

            if (binaryFileInfo != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), binaryFileInfo.Key);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_SourceUrl), binaryFileInfo.SourceUrl);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_SourceName), binaryFileInfo.SourceName);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_FileName), binaryFileInfo.Name);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_FileExtension), binaryFileInfo.FileExetension);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_FileSize), binaryFileInfo.FileSize);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_MD5), binaryFileInfo.MD5);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), binaryFileInfo.Description);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Tags), binaryFileInfo.Tags);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_CreatedStamp), DateTime.Now);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    databaseOperator.ExecuteNonQuery(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }
        }

        /// <summary>
        /// Adds or update <c>ArchivedBinaryArticle</c> object.
        /// </summary>
        /// <param name="key"></param>
        public ArchivedBinaryArticle GetBinaryFile(Guid key)
        {
            const string spName = "sp_GetFile";

            ArchivedBinaryArticle result = null;

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter;

            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), key);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                List<ArchivedBinaryArticle> binaryArticles = ReadBinaryArticle(reader);
                if (binaryArticles != null && binaryArticles.Count > 0)
                {
                    result = binaryArticles[0];
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        #endregion

        #region DomainScope

        /// <summary>
        /// Adds or update <c>DomainScope</c> object.
        /// </summary>
        /// <param name="domainScope"></param>
        /// <returns></returns>
        public Guid? AddOrUpdateDomainScope(DomainScope domainScope)
        {
            const string spName = "sp_AddOrUpdateDomainScope";

            Guid? result = null;

            if (domainScope != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Name), domainScope.Name);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Code), domainScope.Code);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Type), (int)domainScope.Type);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), domainScope.Description);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {

                    result = (Guid?)databaseOperator.ExecuteScalar(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }

            return result;
        }

        /// <summary>
        /// Gets <c>DomainScope</c> objects.
        /// </summary>
        /// <param name="domainScopeType"></param>
        /// <param name="keywords">e.g.: AA,BB,CC</param>
        /// <returns></returns>
        public List<DomainScope> QueryDomainScopes(DomainScopeType? domainScopeType, string keywords)
        {
            const string spName = "sp_QueryDomainScopes";
            List<DomainScope> result = new List<DomainScope>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter;

            parameter = GenerateSqlParameter(
                    GenerateParameterByColumnName(Column_Type),
                    domainScopeType == null ? 0 : (int)(domainScopeType.Value));
            parameters.Add(parameter);

            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Keyword), keywords);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadDomainScopes(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Gets <c>DomainScope</c> object by key.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public DomainScope GetDomainScopeByKey(Guid key)
        {
            const string spName = "sp_GetDomainScopeByKey";
            List<DomainScope> result = new List<DomainScope>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter;

            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), key);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadDomainScopes(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result.Count > 0 ? result[0] : null;
        }

        /// <summary>
        /// Gets <c>DomainScope</c> object by code.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public DomainScope GetDomainScopeByCode(string code)
        {
            const string spName = "sp_GetDomainScopeByCode";
            List<DomainScope> result = new List<DomainScope>();

            if (!string.IsNullOrWhiteSpace(code))
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Code), code);
                parameters.Add(parameter);

                SqlDataReader reader = null;
                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    reader = databaseOperator.ExecuteReader(spName, parameters);
                    result = ReadDomainScopes(reader);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                    databaseOperator.Dispose();
                }
            }

            return result.Count > 0 ? result[0] : null;
        }

        #endregion

        #region Domain Scope User

        /// <summary>
        /// Adds or updates <c>DomainScopeUser</c> object.
        /// </summary>
        /// <param name="domainScopeUser"></param>
        /// <returns>User Key</returns>
        public Guid? AddOrUpdateDomainScopeUser(DomainScopeUser domainScopeUser)
        {
            const string spName = "sp_AddOrUpdateDomainScopeUser";

            Guid? userKey = null;

            if (domainScopeUser != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();

                SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_PersonKey), null);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_DomainScopeKey), domainScopeUser.DomainScope.Key);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ThirdPartyIdentity), domainScopeUser.ThirdPartyIdentity);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), domainScopeUser.Description);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_MonitorStatus), domainScopeUser.MonitorStatus);
                parameters.Add(parameter);

                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    userKey = (Guid?)databaseOperator.ExecuteScalar(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }

            return userKey;
        }

        /// <summary>
        /// Gets <c>DomainScopeUser</c> object by key
        /// </summary>
        /// <param name="key">The key for target object.</param>
        /// <returns>The object instance</returns>
        public DomainScopeUser GetDomainScopeUserByKey(Guid key)
        {
            const string spName = "sp_GetDomainScopeUserByKey";

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), key);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            List<DomainScopeUser> result = new List<DomainScopeUser>();

            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadDomainScopeUsers(reader, false);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result.Count > 0 ? result[0] : null;
        }

        /// <summary>
        /// Query <c>DomainScopeUser</c> objects.
        /// </summary>
        /// <param name="scopeKey"></param>
        /// <param name="identityKeyword"></param>
        /// <returns></returns>
        public List<DomainScopeUser> QueryDomainScopeUser(Guid? scopeKey, string identityKeyword)
        {
            const string spName = "sp_QueryDomainScopeUser";
            List<DomainScopeUser> result = new List<DomainScopeUser>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_DomainScopeKey), scopeKey);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ThirdPartyIdentity), identityKeyword);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadDomainScopeUsers(reader, false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        #endregion

        #region Monitor Clients

        /// <summary>
        /// Gets <c>MonitorClient</c> object.
        /// </summary>
        /// <returns></returns>
        public List<MonitorClient> GetMonitorClients()
        {
            const string spName = "sp_GetMonitorClients";
            List<MonitorClient> result = new List<MonitorClient>();

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName);
                result = ReadMonitorClient(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Gets <c>MonitorClient</c> object by key.
        /// </summary>
        /// <param name="monitorClientKey"></param>
        /// <returns></returns>
        public MonitorClient GetMonitorClientByKey(Guid monitorClientKey)
        {
            const string spName = "sp_GetMonitorClientByKey";
            List<MonitorClient> result = new List<MonitorClient>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), monitorClientKey);
            parameters.Add(parameter);


            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadMonitorClient(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result.Count > 0 ? result[0] : null;
        }

        /// <summary>
        /// Adds <c>MonitorClient</c> object.
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public Guid? AddMonitorClient(MonitorClient client)
        {
            const string spName = "sp_AddMonitorClient";

            Guid? result = null;

            if (client != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Name), client.Name);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), client.Description);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    result = (Guid?)databaseOperator.ExecuteScalar(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }

            return result;
        }

        /// <summary>
        /// Updates <c>MonitorClient</c> object.
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public bool UpdateMonitorClient(MonitorClient client)
        {
            const string spName = "sp_UpdateMonitorClient";

            int result = 0;

            if (client != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), client.Key);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Description), client.Description);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    result = databaseOperator.ExecuteNonQuery(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }

            return result > 0;
        }

        /// <summary>
        /// Query <c>ClientHeartbeat</c> objects.
        /// </summary>
        /// <param name="clientKey"></param>
        /// <param name="topCount"></param>
        /// <returns></returns>
        public List<ClientHeartbeat> QueryClientHearbeat(Guid? clientKey, int topCount)
        {
            const string spName = "sp_QueryHeartbeat";
            List<ClientHeartbeat> result = new List<ClientHeartbeat>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ClientKey), clientKey);
            parameters.Add(parameter);

            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_TopCount), topCount);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadClientHeartbeat(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Creates <c>ClientHeartbeat</c> objects.
        /// </summary>
        /// <param name="clientKey"></param>
        /// <param name="lastSettingStamp"></param>
        /// <returns>Need to retrieve settings</returns>
        public bool CreateClientHeartbeat(Guid clientKey, DateTime lastSettingStamp)
        {
            const string spName = "sp_CreateHeartbeat";

            bool result = false;

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ClientKey), clientKey);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_LastSyncStamp), lastSettingStamp);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
            parameters.Add(parameter);

            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                result = (bool)databaseOperator.ExecuteScalar(spName, parameters);
            }
            catch
            {
                throw;
            }
            finally
            {
                databaseOperator.Dispose();
            }
            return result;
        }

        /// <summary>
        /// Creates <c>MonitorTask</c> object.
        /// </summary>
        /// <param name="task"></param>
        /// <returns></returns>
        public Guid? AddOrUpdateMonitorTask(MonitorTask task)
        {
            const string spName = "sp_AddOrUpdateMonitorTask";
            Guid? result = null;

            if (task != null)
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), task.Key);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Name), task.Name);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_AssignedToClientKey), task.AssignedToClientKey);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ExecutionType), (int)task.ExecutionType);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Options), task.OptionsValue);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_DomainScopeCode), task.DomainScopeCode);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ThirdPartyIdentity), task.TargetIdentity);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_IsNightOperationOnlye), task.IsNightOperationOnly);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_IsDisabled), task.IsDisabled);
                parameters.Add(parameter);
                parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
                parameters.Add(parameter);

                var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
                try
                {
                    result = (Guid?)databaseOperator.ExecuteScalar(spName, parameters);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    databaseOperator.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// Creates <c>MonitorTask</c> object by client key.
        /// </summary>
        /// <param name="clientKey"></param>
        /// <param name="includeDisabled"></param>
        /// <returns></returns>
        public List<MonitorTask> GetMonitorTaskByClientKey(Guid clientKey, bool includeDisabled)
        {
            const string spName = "sp_GetMonitorTaskByClientKey";
            List<MonitorTask> result = new List<MonitorTask>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ClientKey), clientKey);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName("@IncludeDisabled"), includeDisabled);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadMonitorTask(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Creates <c>MonitorTask</c> object by key.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MonitorTask GetMonitorTaskByKey(Guid key)
        {
            const string spName = "sp_GetMonitorTaskByKey";
            List<MonitorTask> result = new List<MonitorTask>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Key), key);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadMonitorTask(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result.Count > 0 ? result[0] : null;
        }

        /// <summary>
        /// Query <c>MonitorTask</c>.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="clientKey"></param>
        /// <param name="executionType"></param>
        /// <param name="thirdPartyIdentity"></param>
        /// <param name="isNightOperationOnly"></param>
        /// <param name="isDisabled"></param>
        /// <returns></returns>
        public List<MonitorTask> QueryMonitorTasks(string name, Guid? clientKey, MonitorTask.TaskExecutionType? executionType, string thirdPartyIdentity, bool? isNightOperationOnly, bool? isDisabled)
        {
            const string spName = "sp_QueryMonitorTasks";
            List<MonitorTask> result = new List<MonitorTask>();

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Name), name);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_AssignedToClientKey), clientKey);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ExecutionType), executionType == null ? null : (int?)(executionType.Value));
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_ThirdPartyIdentity), thirdPartyIdentity);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_IsNightOperationOnlye), isNightOperationOnly);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_IsDisabled), isDisabled);
            parameters.Add(parameter);

            SqlDataReader reader = null;
            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                reader = databaseOperator.ExecuteReader(spName, parameters);
                result = ReadMonitorTask(reader);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                databaseOperator.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Creates <c>ClientHeartbeat</c> objects.
        /// </summary>
        /// <param name="taskKey"></param>
        /// <param name="isSuccess"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public bool CreateTaskExecutionRecord(Guid taskKey, bool isSuccess, string exception)
        {
            const string spName = "sp_CreateTaskExecutionRecord";

            int result = 0;

            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_TaskKey), taskKey);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_IsSuccess), isSuccess);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Exception), exception);
            parameters.Add(parameter);
            parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_Stamp), DateTime.Now);
            parameters.Add(parameter);

            var databaseOperator = new DatabaseOperator(this.CreateSqlConnection());
            try
            {
                result = databaseOperator.ExecuteNonQuery(spName, parameters);
            }
            catch
            {
                throw;
            }
            finally
            {
                databaseOperator.Dispose();
            }

            return result > 0;
        }

        /// <summary>
        /// Query <c>TaskExecutionRecordViewModel</c> objects.
        /// </summary>
        /// <param name="taskKey"></param>
        /// <param name="isSuccess"></param>
        /// <param name="startStamp"></param>
        /// <param name="endStamp"></param>
        /// <returns></returns>
        //public List<TaskExecutionRecordViewModel> QueryTaskExecutionRecord(Guid? taskKey, bool isSuccess, DateTime? startStamp, DateTime? endStamp)
        //{
        //    const string spName = "sp_QueryTaskExecutionRecord";
        //    List<TaskExecutionRecordViewModel> result = new List<TaskExecutionRecordViewModel>();

        //    List<SqlParameter> parameters = new List<SqlParameter>();
        //    SqlParameter parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_TaskKey), taskKey);
        //    parameters.Add(parameter);
        //    parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_TaskKey), isSuccess);
        //    parameters.Add(parameter);
        //    parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_StartStamp), startStamp);
        //    parameters.Add(parameter);
        //    parameter = GenerateSqlParameter(GenerateParameterByColumnName(Column_EndStamp), endStamp);
        //    parameters.Add(parameter);

        //    SqlDataReader reader = SqlHelper.ExecuteReader(this.SqlConnection, spName, parameters);
        //    result = ReadTaskExecutionRecord(reader);

        //    return result;
        //}

        #endregion

        #region Readers

        protected static List<DomainScopeUser> ReadDomainScopeUsers(SqlDataReader reader, bool singleEntityOnly)
        {
            List<DomainScopeUser> result = new List<DomainScopeUser>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    result.Add(ReadDomainScopeUser(reader, singleEntityOnly ? string.Empty : "User"));
                }
            }

            return result;
        }

        protected static DomainScopeUser ReadDomainScopeUser(SqlDataReader reader, string prefix)
        {
            DomainScopeUser obj = new DomainScopeUser();

            obj.Key = (Guid)reader[prefix + Column_Key];
            obj.Description = GetStringValue(reader[prefix + Column_Description]);
            obj.ThirdPartyIdentity = (string)reader[Column_ThirdPartyIdentity];
            obj.MonitorStatus = (bool)reader[Column_MonitorStatus];
            obj.CreatedStamp = (DateTime)reader[prefix + Column_CreatedStamp];
            obj.LastUpdatedStamp = (DateTime)reader[prefix + Column_LastUpdatedStamp];

            if (!string.IsNullOrWhiteSpace(prefix))
            {
                obj.DomainScope = ReadDomainScope(reader, "Scope");
            }
            else
            {
                obj.DomainScope = new DomainScope();
                obj.DomainScope.Key = (Guid)reader[Column_DomainScopeKey];
            }

            return obj;
        }

        protected static List<DomainScope> ReadDomainScopes(SqlDataReader reader)
        {
            List<DomainScope> result = new List<DomainScope>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    result.Add(ReadDomainScope(reader, string.Empty));
                }
            }

            return result;
        }

        protected static DomainScope ReadDomainScope(SqlDataReader reader, string prefix)
        {
            DomainScope obj = new DomainScope();

            obj.Key = (Guid)reader[prefix + Column_Key];
            obj.Name = (string)reader[prefix + Column_Name];
            obj.Code = (string)reader[prefix + Column_Code];
            obj.Type = (DomainScopeType)reader[prefix + Column_Type];
            obj.Description = Convert.IsDBNull(reader[prefix + Column_Description]) ? null : (string)reader[prefix + Column_Description];

            return obj;
        }

        protected static List<MonitorClient> ReadMonitorClient(SqlDataReader reader)
        {
            List<MonitorClient> result = new List<MonitorClient>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    MonitorClient obj = new MonitorClient();
                    obj.Key = (Guid)reader[Column_Key];
                    obj.Name = (string)reader[Column_Name];
                    obj.CreatedStamp = (DateTime)reader[Column_CreatedStamp];
                    obj.LastUpdatedStamp = (DateTime)reader[Column_LastUpdatedStamp];
                    obj.LastSyncStamp = Convert.IsDBNull(reader[Column_LastSyncStamp]) ? null : (DateTime)reader[Column_LastSyncStamp] as DateTime?;


                    result.Add(obj);
                }
            }

            return result;
        }

        protected static List<ClientHeartbeat> ReadClientHeartbeat(SqlDataReader reader)
        {
            List<ClientHeartbeat> result = new List<ClientHeartbeat>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    ClientHeartbeat obj = new ClientHeartbeat();
                    obj.Key = (Guid)reader[Column_Key];
                    obj.ClientName = (string)reader[Column_Name];
                    obj.ClientKey = (Guid)reader[Column_ClientKey];
                    obj.CreatedStamp = (DateTime)reader[Column_CreatedStamp];
                    result.Add(obj);
                }
            }

            return result;
        }

        protected static List<MonitorTask> ReadMonitorTask(SqlDataReader reader)
        {
            List<MonitorTask> result = new List<MonitorTask>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    MonitorTask obj = new MonitorTask();
                    obj.Key = (Guid)reader[Column_Key];
                    obj.Name = (string)reader[Column_Name];
                    obj.AssignedToClientKey = (Guid)reader[Column_AssignedToClientKey];
                    obj.ExecutionType = (MonitorTask.TaskExecutionType)reader[Column_ExecutionType];
                    obj.OptionsValue = (int)reader[Column_Options];
                    obj.DomainScopeCode = (string)reader[Column_DomainScopeCode];
                    obj.TargetIdentity = (string)reader[Column_ThirdPartyIdentity];
                    obj.IsNightOperationOnly = (bool)reader[Column_IsNightOperationOnlye];
                    obj.IsDisabled = (bool)reader[Column_IsDisabled];
                    obj.CreatedStamp = (DateTime)reader[Column_CreatedStamp];
                    obj.LastUpdatedStamp = (DateTime)reader[Column_LastUpdatedStamp];
                    obj.LastExecutedStamp = (DateTime)reader[Column_LastSyncStamp];
                    result.Add(obj);
                }
            }

            return result;
        }

        //protected static List<TaskExecutionRecordViewModel> ReadTaskExecutionRecord(SqlDataReader reader)
        //{
        //    List<TaskExecutionRecordViewModel> result = new List<TaskExecutionRecordViewModel>();

        //    if (reader != null)
        //    {
        //        while (reader.Read())
        //        {
        //            TaskExecutionRecordViewModel obj = new TaskExecutionRecordViewModel();
        //            obj.RecordKey = (Guid)reader[Prefix_Record + Column_Key];
        //            obj.TaskKey = (Guid)reader[Prefix_Task + Column_Key];
        //            obj.IsSuccess = (bool)reader[Column_IsSuccess];
        //            obj.Exception = Convert.IsDBNull(reader[Column_Exception]) ? null : (string)reader[Column_Exception];
        //            obj.CreatedStamp = (DateTime)reader[Prefix_Record + Column_CreatedStamp];
        //            obj.ExecutionType = (MonitorTask.TaskExecutionType)reader[Prefix_Task + Column_ExecutionType];
        //            obj.TaskName = (string)reader[Prefix_Task + Column_Name];
        //            obj.DomainScopeCode = (string)reader[Column_DomainScopeCode];
        //            obj.DomainScopeName = (string)reader[Prefix_DomainScope + Column_Name];
        //            obj.TargetIdentity = (string)reader[Prefix_Task + Column_ThirdPartyIdentity];
        //            obj.ClientName = (string)reader[Prefix_Client + Column_DomainScopeCode];

        //            result.Add(obj);
        //        }
        //    }

        //    return result;
        //}

        protected static List<ThridPartyUrl> ReadThridPartyUrl(SqlDataReader reader)
        {
            List<ThridPartyUrl> result = new List<ThridPartyUrl>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    ThridPartyUrl obj = new ThridPartyUrl();
                    obj.Url = GetStringValue(reader[Column_Url]);
                    obj.ActualUrl = GetStringValue(reader[Column_ActualUrl]);
                    obj.Title = GetStringValue(reader[Column_Title]);
                    obj.Description = GetStringValue(reader[Column_Description]);
                    obj.CreatedStamp = (DateTime)reader[Column_CreatedStamp];
                    obj.CreatedStamp = (DateTime)reader[Column_LastUpdatedStamp];
                    result.Add(obj);
                }
            }

            return result;
        }

        protected static List<ArchivedBinaryArticle> ReadBinaryArticle(SqlDataReader reader)
        {
            List<ArchivedBinaryArticle> result = new List<ArchivedBinaryArticle>();

            if (reader != null)
            {
                while (reader.Read())
                {
                    ArchivedBinaryArticle obj = new ArchivedBinaryArticle();
                    obj.Key = (Guid)reader[Column_Key];
                    obj.SourceUrl = GetStringValue(reader[Column_SourceUrl]);
                    obj.SourceName = GetStringValue(reader[Column_SourceName]);
                    obj.Name = GetStringValue(reader[Column_FileName]);
                    obj.FileExetension = GetStringValue(reader[Column_FileExtension]);
                    obj.FileSize = GetIntValue(reader[Column_FileSize], 0);
                    obj.MD5 = GetStringValue(reader[Column_MD5]);
                    obj.Description = GetStringValue(reader[Column_Description]);
                    obj.Tags = GetStringValue(reader[Column_Tags]);
                    obj.CreatedStamp = (DateTime)reader[Column_CreatedStamp];
                    result.Add(obj);
                }
            }

            return result;
        }

        #endregion

        /// <summary>
        /// Generate sql parameter.
        /// </summary>
        /// <param name="parameterName">The parameter name.</param>
        /// <param name="value">The value.</param>
        /// <returns>The sql parameter.</returns>
        protected static SqlParameter GenerateSqlParameter(string parameterName, object value)
        {
            SqlParameter sqlParameter = new SqlParameter(parameterName, value);
            if (value == null)
            {
                sqlParameter.Value = DBNull.Value;
            }
            return sqlParameter;
        }

        protected static string GenerateParameterByColumnName(string column)
        {
            return "@" + column;
        }

        protected static bool RetrieveLastStatusByIdentity(Guid scopeKey, string thirdPartyIdentity)
        {
            return false;
        }

        protected static string GetStringValue(object obj)
        {
            string result = null;

            if (obj != null && !Convert.IsDBNull(obj))
            {
                result = (string)obj;
            }

            return result;
        }

        protected static int? GetNullableIntValue(object obj)
        {
            int? result = null;

            if (obj != null && !Convert.IsDBNull(obj))
            {
                result = (int)obj;
            }

            return result;
        }

        protected static int GetIntValue(object obj, int defaultValue)
        {
            int result = defaultValue;

            if (obj != null && !Convert.IsDBNull(obj))
            {
                result = (int)obj;
            }

            return result;
        }
    }
}
